From ad3781962305c33b99defabdcf00619ef47903b8 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 31 Jul 2026 16:36:13 +0000 Subject: [PATCH] Add the CYCLE_STATES.SSRank column --- conversion/load_support.sql | 18 +++++------ .../codes/indexes/create/cycle_states.m4 | 5 +++ db/schemas/codes/indexes/drop/cycle_states.m4 | 1 + .../codes/tables/create/cycle_states.m4 | 2 ++ doc/src/code_tables.m4 | 31 +++++++++++++++++++ doc/src/epilog.inc.m4 | 2 ++ 6 files changed, 50 insertions(+), 9 deletions(-) diff --git a/conversion/load_support.sql b/conversion/load_support.sql index 1a01524..4ccd1c1 100644 --- a/conversion/load_support.sql +++ b/conversion/load_support.sql @@ -88,15 +88,15 @@ insert into comm_ids (commid, name, notes, membcriteria) , ('KL_KK', 'Kasekela/kalande', '', ''); -- cycle_states -INSERT INTO cycle_states (code, asnum, description) - VALUES ('0', 0, 'Not swollen') - , ('0.25', 0.25, '1/4 swollen') - , ('0.5', 0.5, '1/2 swollen') - , ('0.75', 0.75, '3/4 swollen') - , ('1', 1.0, 'Fully swollen') - , ('U', NULL, 'Adolescent swelling') - , ('n/a', NULL, 'Male') - , ('MISS', NULL, 'Missing data'); -- problem #42 +INSERT INTO cycle_states (code, asnum, ssrank, description) + VALUES ('0', 0, 2, 'Not swollen') + , ('0.25', 0.25, 3, '1/4 swollen') + , ('0.5', 0.5, 4, '1/2 swollen') + , ('0.75', 0.75, 5, '3/4 swollen') + , ('1', 1.0, 6, 'Fully swollen') + , ('U', NULL, 1, 'Adolescent swelling') + , ('n/a', NULL, NULL, 'Male') + , ('MISS', NULL, NULL, 'Missing data'); -- problem #42 -- dad_statuses INSERT INTO dad_statuses (status, description) diff --git a/db/schemas/codes/indexes/create/cycle_states.m4 b/db/schemas/codes/indexes/create/cycle_states.m4 index 8908283..7d9fc78 100644 --- a/db/schemas/codes/indexes/create/cycle_states.m4 +++ b/db/schemas/codes/indexes/create/cycle_states.m4 @@ -25,3 +25,8 @@ CREATE UNIQUE INDEX IF NOT EXISTS cycle_states_code_uniquenocase ON cycle_states (sdb_case_equivalence(code)); +CREATE UNIQUE INDEX IF NOT EXISTS "On CYCLE_STATES, SSRank must be unique" + ON cycle_states + (ssrank) + WHERE ssrank IS NOT NULL; + diff --git a/db/schemas/codes/indexes/drop/cycle_states.m4 b/db/schemas/codes/indexes/drop/cycle_states.m4 index 899847a..10f9abe 100644 --- a/db/schemas/codes/indexes/drop/cycle_states.m4 +++ b/db/schemas/codes/indexes/drop/cycle_states.m4 @@ -22,3 +22,4 @@ include(`constants.m4')dnl include(`macros.m4')dnl DROP INDEX IF EXISTS cycle_states_code_uniquenocase; +DROP INDEX IF EXISTS "On CYCLE_STATES, SSRank must be unique"; diff --git a/db/schemas/codes/tables/create/cycle_states.m4 b/db/schemas/codes/tables/create/cycle_states.m4 index 4533498..a2728d6 100644 --- a/db/schemas/codes/tables/create/cycle_states.m4 +++ b/db/schemas/codes/tables/create/cycle_states.m4 @@ -30,6 +30,8 @@ CREATE TABLE cycle_states ( ,asnum DOUBLE PRECISION CONSTRAINT "AsNum must be between 0 and 1, inclusive" CHECK (0 <= asnum AND asnum <= 1) + ,ssrank INTEGER + positive_check(`SSRank') ,description TEXT NOT NULL emptytext_check(`Description') ); diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 509b3bc..514efaa 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -340,6 +340,14 @@ CYCLE_STATES This table defines the metric for sexual swelling measures. +CYCLE_STATES contains two columns that rank swelling codes from +minimum to maximum swelling. +The |CYCLE_STATES.AsNum| column ranks swelling codes, and is suitable +for use in computations when querying. +The |CYCLE_STATES.SSRank| column ranks swelling codes, but its +intended use is in automated processes that build the content of the +|SWELLING_STATES| table. + Special Values `````````````` @@ -414,6 +422,29 @@ makes no sense, such as ``sdb_missing_swelling`` or .. the problem. +.. _CYCLE_STATES.SSRank: + +SSRank (SWELLING_STATES Rank) +''''''''''''''''''''''''''''' + +.. |CYCLE_STATES.SSRank_summary| replace:: + A positive integer which ranks the swelling codes for the purpose + of determining the |SWELLING_STATES|.\ + |SWELLING_STATES.SwellingMin| and |SWELLING_STATES|.\ + |SWELLING_STATES.SwellingMax| values. + An individual's |CYCLE_STATES.Code| with the largest value becomes + the maximum value, and that having the smallest value becomes the + minimum. + +|CYCLE_STATES.SSRank_summary| + +This column may be |null|. +When it is |null|, the |CYCLE_STATES.Code| will *not* appear in the +|SWELLING_STATES| table. + +|nullunique| + + .. _CYCLE_STATES.Description: Description diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 28ed1bb..32c8d80 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -413,6 +413,8 @@ sdb_generated_rst()dnl :ref:`Code ` .. |CYCLE_STATES.AsNum| replace:: :ref:`AsNum ` +.. |CYCLE_STATES.SSRank| replace:: + :ref:`SSRank ` .. |CYCLE_STATES.Description| replace:: :ref:`Description ` -- 2.34.1